<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class frontmodel extends CI_Model {
 function __construct()
        {
            parent::__construct();
        }
 /* function login($email,$password)
 {
  $this->db->where("email",$email);
  $this->db->where("password",$password);

  $query=$this->db->get("user");
  if($query->num_rows()>0)
  {
   foreach($query->result() as $rows)
   {
    //add all data to session
    $newdata = array(
      'user_id'  => $rows->id,
      'user_name'  => $rows->username,
      'user_email'    => $rows->email,
      'logged_in'  => TRUE,
    );
   }
   $this->session->set_userdata($newdata);
   return true;
  }
  return false;
 } */
 public function add_user()
 {
 
  $data=array(
    'USERNAME'=>$this->input->post('user_name'),
    'EMAIL'=>$this->input->post('email'),
    'PASSWORD'=>md5($this->input->post('password')),
	'ROLE'=>('5'),
	'STATUS'=>('0')
  );
  $this->db->insert('user',$data);
  /* $sql = "INSERT INTO user (STATUS) VALUES('1')";
  $this->db->query($sql); */
 }
 public function aktivasi_user($register_code)
 {
 $data=array(
    
	'STATUS'=>('1')
  );
 /* $email   = $this->input->post('email_address'); */
 $this->db->where('PASSWORD',$register_code);
$this->db->update('user',$data);


  
 }
 
 function cekdb() { 
    $username = $this->input->post('user_name'); 
    $pass = md5($this->input->post('password'));
    
    $this->db->where('USERNAME', $username);
    $this->db->where('PASSWORD', $pass);
	$this->db->where('STATUS', '1');
    $query = $this->db->get('user'); 
    return $query->result(); 
  }
  
  function ceklupa() { 
    $email = $this->input->post('email_address'); 
   
    
    $this->db->where('EMAIL', $email);
  
	$this->db->where('STATUS', '1');
    $query = $this->db->get('user'); 
    return $query->result(); 
  }
  
  function cekuser($str){
  $this->db->where('USERNAME', $str);
 
  $query = $this->db->get('user'); 
    return $query->result();
  }
  
  
   function cekemail($str){
  $this->db->where('EMAIL', $str);
 
  $query = $this->db->get('user'); 
    return $query->result();
  }
  
  function ubahPass(){
  $passlama = md5($this->input->post('pass_lama'));
	$passbaru = md5($this->input->post('conpass'));
  $data=array(
    
	'PASSWORD'=>$passbaru
  );
  $this->db->where('PASSWORD', $passlama);
  $this->db->update('user',$data);
  
  }
  
  function add_pemohon(){
		
		$email   = $this->input->post('email');

		$q = $this->db->query('SELECT ID_USER FROM user WHERE EMAIL = ?',array($email));
		$data = array_shift($q->result_array());
		$datass = $data['ID_USER'];

  
  
  
		  $data=array(
			'NAMA_LENGKAP'=>$this->input->post('nama_lengkap'),
			'ID_USER'=>$data['ID_USER']
			
		  );
		  
		
				
		  
		  
		  
		  $this->db->insert('pemohon',$data);
		    
		  
  
  }
  
  function update_user(){
			$email   = $this->input->post('email');

			$q = $this->db->query('SELECT ID_USER FROM user WHERE EMAIL = ?',array($email));
			$data = array_shift($q->result_array());
			$id_user = $data['ID_USER'];
			 
			$q = $this->db->query('SELECT ID_PEMOHON FROM pemohon WHERE ID_USER = ?',array($id_user));
					$data = array_shift($q->result_array());
			  $data=array(
				
				'ID_PEMOHON'=>$data['ID_PEMOHON']
				
			  );
			  
			  
			  $this->db->where('ID_USER', $id_user);
			  $this->db->update('user',$data);
  }
  
  function get_pemohon($username){
  
  

		$q = $this->db->query('SELECT ID_USER,ID_PEMOHON FROM user WHERE USERNAME = ?',array($username));
		$data = array_shift($q->result_array());
		$datas = $data['ID_USER'];
	/* 	
		echo $datas; */
		
		
		 
   
	
		$this->db->select('p.ID_PEMOHON, p.NAMA_LENGKAP, p.TEMPAT_LAHIR, p.TGL_LAHIR, p.JENIS_KELAMIN, p.ALAMAT, p.TELP, p.TK1, p.TK2, p.TK3, p.ALAMAT_TK1, p.ALAMAT_TK2, p.ALAMAT_TK3, p.ALAMAT_KTP, p.HP, u.EMAIL, u.USERNAME, u.ROLE, u.STATUS');
		$this->db->from('user u, pemohon p');
		$this->db->where('u.ID_USER = p.ID_USER');
		$this->db->where("u.ID_USER = $datas");
		$query = $this->db->get();
		return $query->result(); 
  }
  
  public function updateBio($username){
		$datelawas = $this->input->post('tanggal_lahir');
		
		$tanggal = date("Y-m-d", strtotime($datelawas));
		
		$q = $this->db->query('SELECT ID_USER FROM user WHERE USERNAME = ?',array($username));
		$data = array_shift($q->result_array());
		$datas = $data['ID_USER'];
		
		
		$datapemohon = $this->get_pemohon($username);
		foreach($datapemohon as $row){
		$k1 = $row->TK1;
		$k2 = $row->TK2;
		$k3 = $row->TK3;
		}
			if (empty($k1)){
			
			if(!($this->input->post('sd'))){
			$tk1= '';
			}
			else {
			$tk1 = $this->input->post('sd');
			}
			}
			else{
			$tk1 = $k1;
			}
			
			if (empty($k2)){
			if(!($this->input->post('smp'))){
			$tk2= '';
			}
			else {
			$tk2 = $this->input->post('smp');
			}
			}
			else{
			$tk2 = $k2;
			}
			
			if (empty($k3)){
			if(!($this->input->post('sma'))){
			$tk3= '';
			}
			else {
			$tk3 = $this->input->post('sma');
			}
			}
			else{
			$tk3 = $k3;
			}
			$datass=array(
				
				
				'NAMA_LENGKAP'=>$this->input->post('nama_lengkap'),
				'TEMPAT_LAHIR'=>$this->input->post('tempat_lahir'),
				'TGL_LAHIR'=>$tanggal,
				'JENIS_KELAMIN'=>$this->input->post('jenis_kelamin'),
				'ALAMAT'=>$this->input->post('alamat'),
				'TELP'=>$this->input->post('nomor_telepon'),
				'HP'=>$this->input->post('hp'),
				'TK1'=>$tk1,
				'TK2'=>$tk2,
				'TK3'=>$tk3
				
			  );
			

			$this->db->where('ID_USER', $datas);
			$this->db->update('pemohon', $datass);
	}


public function updateBio2($username){
			$datelawas = $this->input->post('tanggal_lahir');
		
			$tanggal = date("Y-m-d", strtotime($datelawas));

			$q = $this->db->query('SELECT ID_USER FROM user WHERE USERNAME = ?',array($username));
		$data = array_shift($q->result_array());
		$datas = $data['ID_USER'];
		
		foreach($datapemohon as $row){
		$k1 = $row->TK1;
		$k2 = $row->TK2;
		$k3 = $row->TK3;
		}
			if (empty($k1)){
			
			if(!($this->input->post('sd'))){
			$tk1= '';
			}
			else {
			$tk1 = $this->input->post('sd');
			}
			}
			else{
			$tk1 = $k1;
			}
			
			if (empty($k2)){
			if(!($this->input->post('smp'))){
			$tk2= '';
			}
			else {
			$tk2 = $this->input->post('smp');
			}
			}
			else{
			$tk2 = $k2;
			}
			
			if (empty($k3)){
			if(!($this->input->post('sma'))){
			$tk3= '';
			}
			else {
			$tk3 = $this->input->post('sma');
			}
			}
			else{
			$tk3 = $k3;
			}
		
		
			$datass=array(
				
				
				'TK1'=>$this->$tk1,
				'TK2'=>$this->$tk2,
				'TK3'=>$this->$tk2
			  );
			

			$this->db->where('ID_USER', $datas);
			$this->db->update('pemohon', $datass);
  }




  
  function add_pengajuan($id_user){
  /* $datelawas = $this->input->post('tanggal_permohonan'); */
		/* $tanggal=$date->format('Y-m-d'); */
	
		$tanggal 	= date('Y-m-d');
		
		$q 			= $this->db->query('SELECT ID_PEMOHON FROM pemohon WHERE ID_USER = ?',array($id_user));
		$data 		= array_shift($q->result_array());
		$datas 		= $data['ID_PEMOHON'];
		
		$cek1		= $this->db->query('select * from data_sekolah where NAMA = "'.$this->input->post('sd').'"');
		$cek2		= $this->db->query('select * from data_sekolah where NAMA = "'.$this->input->post('smp').'"');
		$cek3		= $this->db->query('select * from data_sekolah where NAMA = "'.$this->input->post('sma').'"');
		
		$result1 = array_shift($cek1->result_array());
		$result2 = array_shift($cek2->result_array());
		$result3 = array_shift($cek3->result_array());
		
		$update_pemohon = array(
			'STATUS_TK1'	=> $result1['STATUS'],
			'STATUS_TK2'	=> $result2['STATUS'],
			'STATUS_TK3'	=> $result3['STATUS']
		);
		
		$this->db->where('ID_PEMOHON',$datas);
		$this->db->update('pemohon',$update_pemohon);
		
		$data=array(
			'ID_PEMOHON' 	=> $datas,
			'TGL_PENGAJUAN'	=>$tanggal,
			'ALASAN' 		=> $this->input->post('alasan'),
			'LEMBAR_TK1'	=>$this->input->post('account1'),
			'LEMBAR_TK2'	=>$this->input->post('account2'),
			'LEMBAR_TK3'	=>$this->input->post('account3'),
			'VERIFIKATOR'	=>$this->input->post('account4'),
			'STATUS'		=>'1'
		  );
		$this->db->insert('pengajuan',$data);
		return $this->db->insert_id();
  }
  
  function getDataSekolah($term)
    {
        $sql = $this->db->query('select * from data_sekolah where NAMA like "'. mysql_real_escape_string($term) .'%" order by NAMA asc limit 0,10');

		return $sql ->result();
    } 
	
	function cekPemohon($id_user){

	$q = $this->db->query('SELECT ID_PEMOHON FROM pemohon WHERE ID_USER = ?',array($id_user));
		$data = array_shift($q->result_array());
		$datas = $data['ID_PEMOHON'];

	 $this->db->where('ID_PEMOHON', $datas);
   
    $query = $this->db->get('pengajuan'); 
    return $query->result();
	
	}

	function getEmailByJabatan($jabatan)
	{
		//select id sekretaris
		$q = $this->db->query("SELECT ID_USER FROM pegawai WHERE JABATAN = $jabatan");
		$data = array_shift($q->result_array());

		//select email sekretaris
		$q = $this->db->query('SELECT EMAIL FROM user WHERE ID_USER = '.$data['ID_USER']);
		$data = array_shift($q->result_array());

		//return email
		return $data['EMAIL'];
		
	}
 
}



?>